package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.Organization;
import com.xmy.cultivate.entity.SchoolNeeds;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import cn.hutool.db.Page;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.SchoolNeeds;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.TryJob;
import com.xmy.cultivate.entity.response.TidyRs;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 校区招聘需求表 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-12-11
 */
public interface SchoolNeedsMapper extends BaseMapper<SchoolNeeds> {

    @Select({"<script>",
            "SELECT s.*,getSchoolName(s.school_id) AS schoolName, " ,
            "( SELECT COUNT( * ) FROM adms_try_job t WHERE t.school_id = s.school_id AND  s.station_id = t.station_id AND t.is_arrive_job = 0 " ,
            "<if test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND t.try_date BETWEEN #{startDate} AND #{endDate} " ,
            "</if>",
            ") AS allocateCount, ",
            "( SELECT COUNT( * ) FROM adms_try_job t WHERE t.school_id = s.school_id AND  s.station_id = t.station_id AND t.pass_type = 1  " ,
            "<if test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND t.try_audit_date BETWEEN #{startDate} AND #{endDate} " ,
            "</if>",
            ") AS tryJobPassCount, ",
            "( SELECT COUNT( * ) FROM adms_try_job t WHERE t.school_id = s.school_id AND  s.station_id = t.station_id AND t.pass_type = 2  " ,
            "<if test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND t.try_audit_date BETWEEN #{startDate} AND #{endDate} " ,
            "</if>",
            ") AS tryJobNoPassCount ",
            "FROM adms_school_needs s",
            "WHERE " ,
            "1=1" ,
//            "<if test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
//            "AND t.try_date &gt;= #{startDate} AND t.try_date &lt;= #{endDate}",
//            "</if>",
            "<if test='stationId!=null and stationId!=&apos;&apos;'>",
//            "AND s.station_id = #{stationId} ",
            "AND s.station_id IN ( ${stationId} )",
            "AND s.need_count > 0",
            "</if>",
            "ORDER BY s.sort ",
            "</script>"})
    List<SchoolNeeds> querySchoolNeedList( @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("stationId") String stationId);

    @Select({"<script>",
            "SELECT school_id, schoolName, SUM(need_count) AS needCount, allocateCount AS allocateCount,",
            "tryJobPassCount AS tryJobPassCount,tryJobNoPassCount AS tryJobNoPassCount",
            "FROM (",
            "SELECT s.*, getSchoolName(s.school_id) AS schoolName,",
            "(SELECT COUNT(*) FROM adms_try_job t WHERE t.school_id = s.school_id AND t.is_arrive_job = 0" ,
            "<if test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND t.try_date BETWEEN #{startDate} AND #{endDate}" ,
            "</if>",

            "<if test='stationId!=null and stationId!=&apos;&apos;'>",
            "AND t.station_id IN ( ${stationId} )",
            "</if>",

            ") AS allocateCount,",
            "(SELECT COUNT(*) FROM adms_try_job t WHERE t.school_id = s.school_id " ,
            "<if test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND t.try_audit_date BETWEEN #{startDate} AND #{endDate} AND t.pass_type = 1" ,
            "</if>",

            "<if test='stationId!=null and stationId!=&apos;&apos;'>",
            "AND t.station_id IN ( ${stationId} )",
            "</if>",

            ") AS tryJobPassCount,",
            "(SELECT COUNT(*) FROM adms_try_job t WHERE t.school_id = s.school_id " ,
            "<if test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND t.try_audit_date BETWEEN #{startDate} AND #{endDate} AND t.pass_type = 2" ,
            "</if>",

            "<if test='stationId!=null and stationId!=&apos;&apos;'>",
            "AND t.station_id IN ( ${stationId} )",
            "</if>",

            ") AS tryJobNoPassCount",
            "FROM adms_school_needs s",
            "WHERE 1 = 1",
            "AND s.need_count > 0",

            "<if test='stationId!=null and stationId!=&apos;&apos;'>",
            "AND s.station_id IN ( ${stationId} )",
            "</if>",

            "ORDER BY s.school_id",
            ") AS subquery",
            "GROUP BY school_id",

            "</script>"})
    List<SchoolNeeds> querySchoolNeedTotal( @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("stationId") String stationId);

    @Select({"<script>",
            "SELECT j.*, s.name as stationName, " ,
            "t2.name as invitation_name,",
            "getSchoolName ( j.school_id ) AS school_name," ,
            "r.name,t1.subjects_id FROM adms_try_job j " ,
            "LEFT JOIN adms_recruit r ON r.id = j.recruit_id " ,
            "LEFT JOIN adms_school_needs t1 ON t1.school_id = j.school_id  " ,
            "AND j.station_id = t1.station_id",
            "LEFT JOIN adms_station s ON s.id = j.station_id",
            "LEFT JOIN staff AS t2 ON t2.id = r.inviter_id ",
            "WHERE " ,
            "1 = 1" ,
            "AND j.is_arrive_job = 0",
            "<if test='type == 2 and type!=null and type!=&apos;&apos;'>",
            "AND j.pass_type = 1" ,
            "</if>" ,
            "<if test='type == 3 and type!=null and type!=&apos;&apos;'>",
            "AND j.pass_type = 2" ,
            "</if>" ,
            "<if test='schoolId!=null and schoolId!=&apos;&apos;'>",
//            "AND j.school_id = #{schoolId}" ,
            "AND j.school_id IN (${schoolId})" ,
            "</if>" ,
            "<if test='stationId!=null and stationId!=&apos;&apos;'>",
//            "AND j.station_id = #{stationId}" ,
            "AND j.station_id IN (${stationId})" ,
            "</if>" ,
            "<if test='type == 1 and startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND j.try_date BETWEEN #{startDate} AND #{endDate}" ,
            "</if>" ,
            "<if test='(type == 2 or type == 3) and startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND j.try_audit_date BETWEEN #{startDate} AND #{endDate}" ,
            "</if>" ,
            "ORDER BY j.try_date DESC" ,
            "</script>"})
    List<TryJob> queryTryJobBySchoolId(IPage<TryJob> page, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("schoolId") String schoolId, @Param("stationId") String stationId, @Param("type") String type);

//原来
 /*   @Select({"<script>",
            "SELECT j.*, " ,
            "getSchoolName ( j.school_id ) AS school_name," ,
            "r.name,t1.subjects_id FROM adms_try_job j " ,
            "LEFT JOIN adms_recruit r ON r.id = j.recruit_id " ,
            "LEFT JOIN adms_school_needs t1 ON t1.school_id = j.school_id  " ,
            "AND j.subjects_id = t1.subjects_id",
            "WHERE " ,
            "1 = 1" ,
            "<if test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND j.school_id = #{schoolId}" ,
            "</if>" ,
            "<if test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND j.subjects_id = #{subjectsId}" ,
            "</if>" ,
            "<if test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND j.try_date BETWEEN #{startDate} AND #{endDate}" ,
            "</if>" ,
            "ORDER BY j.try_date DESC" ,
            "</script>"})
    List<TryJob> queryTryJobBySchoolId(IPage<TryJob> page, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId);
*/

    @Select({"<script>",
            "WITH with_tb01 AS (",
                "SELECT ",
                "t1.*,",
                "t2.`name` AS station_needs_name,",
                "t3.`name` AS  department_name ",
                "FROM adms_school_needs AS t1",
                "INNER JOIN adms_station_needs AS t2 ON t1.station_needs_id = t2.id ",
                "LEFT JOIN department AS t3 ON t1.department_id = t3.id",
                "WHERE 1=1",
//                "AND t1.need_count &gt; 0",
                "<when test='stationNeedsId!=null and stationNeedsId!=&apos;&apos; '>",
                    "AND t1.station_needs_id in(${stationNeedsId})",
                "</when>",
                "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                    "AND t1.school_id in(${schoolId})",
                "</when>",
                "<when test='departmentId!=null and departmentId!=&apos;&apos; '>",
                    "AND t1.department_id in(${departmentId})",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "AND t1.subjects_id in(${subjectsId})",
                "</when>",
                "<when test='type!=null and type!=&apos;&apos; and type != 0'>",
                    "AND t2.type = ${type}",
                "</when>",
                "<when test='labourId!=null and labourId!=&apos;&apos; '>",
                    "AND t2.labour_id IN (${labourId})",
                "</when>",
                "<when test='stationId!=null and stationId!=&apos;&apos; '>",
                    "AND t2.station_id IN (${stationId})",
                "</when>",
                "<when test='departmentId!=null and departmentId!=&apos;&apos; '>",
                    "AND t2.department_id IN (${departmentId})",
                "</when>",
            "),",
            "with_tb02 AS (  ",
                "SELECT department_id,department_name,school_id,station_needs_name,",
//                "station_needs_id AS station_needs_ids,",
                "GROUP_CONCAT(station_needs_id) AS station_needs_ids,",
                "SUM( need_count ) AS needCount ",
                "FROM with_tb01 ",
                "GROUP BY department_id,school_id",
                "<if test='isDetails == 1'>",
                    ",station_needs_id ",
                "</if>",
            ")",

            "SELECT",
            "*,",
            "( needCount - tryJobRecruitCount ) AS needRecruitment,",
            "( departmentCount + schoolCount ) AS allocateCount",
            "FROM",
            "(",
                "SELECT",
                "t01.id,",
                "with_tb02.school_id,",
                "with_tb02.station_needs_ids,",
                "with_tb02.station_needs_name,",
                "with_tb02.department_id,",
                "getSchoolName ( with_tb02.school_id ) AS schoolName,",
                "with_tb02.department_name,",
                "with_tb02.needCount,",

                "SUM( CASE WHEN role_id = 1 ",
                "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                    "AND try_date BETWEEN '${startDate}' AND '${endDate}' ",
                "</when>",
                "THEN 1 ELSE 0 END ) AS departmentCount,",

                "SUM( CASE WHEN role_id = 2 ",
                "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                    "AND try_date BETWEEN '${startDate}' AND '${endDate}' ",
                "</when>",
                "THEN 1 ELSE 0 END ) AS schoolCount,",

                "SUM( CASE WHEN pass_type = 1 ",
                "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                    "AND try_audit_date BETWEEN '${startDate}' AND '${endDate}' ",
                "</when>",
                "THEN 1 ELSE 0 END ) AS tryJobPassCount,",

                "SUM( CASE WHEN pass_type = 2 ",
                "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                    "AND try_audit_date BETWEEN '${startDate}' AND '${endDate}' ",
                "</when>",
                "THEN 1 ELSE 0 END ) AS tryJobNoPassCount,",

                "SUM( CASE WHEN pass_type = 0 ",
                "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                    "AND try_date BETWEEN '${startDate}' AND '${endDate}' ",
                "</when>",
                "THEN 1 ELSE 0 END ) AS tryJobIngCount, ",

                "SUM( CASE WHEN pass_type IN ( 0, 1 ) THEN 1 ELSE 0 END ) AS tryJobRecruitCount ",

                "FROM with_tb02",
                "LEFT JOIN (",
                    "SELECT",
                    "t1.*,",
                    "t2.`name`,",
                    "t2.phone,",
                    "t2.inviter_id,",
                    "CASE",
                    "WHEN EXISTS ( SELECT 1 FROM role_admin WHERE admin_id = t2.inviter_id AND role_id = 1568160915052400644 ) THEN",
                    "1 ELSE 2 ",
                    "END AS role_id ",
                    "FROM",
                    "adms_try_job AS t1",
                    "INNER JOIN adms_recruit AS t2 ON t2.id = t1.recruit_id ",
                    "WHERE 1=1",
                    "AND is_arrive_job = 0",
                    "<when test='stationNeedsId!=null and stationNeedsId!=&apos;&apos; '>",
                        "AND t1.station_needs_id in(${stationNeedsId})",
                    "</when>",
                    "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                        "AND t1.school_id in(${schoolId})",
                    "</when>",
                    "<when test='departmentId!=null and departmentId!=&apos;&apos; '>",
                        "AND t1.department_id in(${departmentId})",
                    "</when>",
                ") AS t01 ON ",
                "<when test='type==null or type==&apos;&apos; '>",
                    "(( with_tb02.department_id IS NOT NULL AND with_tb02.department_id = t01.department_id ) OR ( with_tb02.department_id IS NULL AND with_tb02.school_id = t01.school_id ))",
                "</when>",
                "<when test='type!=null and type!=&apos;&apos; '>",
                    "<if test='type == 1'>",
                        "with_tb02.school_id = t01.school_id ",
                    "</if>",
                    "<if test='type == 2'>",
                        "with_tb02.department_id = t01.department_id ",
                    "</if>",
                "</when>",
                "AND FIND_IN_SET(t01.station_needs_id, with_tb02.station_needs_ids)",
                "<if test='isDetails == 1'>",
//                    "AND with_tb02.station_needs_ids = t01.station_needs_id",
                "</if>",
            "GROUP BY COALESCE ( with_tb02.department_id, with_tb02.school_id )",
                "<if test='isDetails == 1'>",
                    ",with_tb02.station_needs_ids ",
                "</if>",
            ") AS tt ",
            "WHERE",
            "needCount &gt; 0 ",
            "AND ( school_id IS NOT NULL OR department_id IS NOT NULL)",
            "</script>"})
    List<SchoolNeeds> getAssignList(@Param("startDate") String startDate, @Param("endDate") String endDate, @Param("stationNeedsId") String stationNeedsId, @Param("schoolId") String schoolId, @Param("departmentId") String departmentId, @Param("isDetails") Integer isDetails, @Param("type") Integer type, @Param("subjectsId") String subjectsId, @Param("labourId") String labourId, @Param("stationId") String stationId);

    @Select({"<script>",
            "WITH with_tb01 AS (",
                "SELECT",
                "t1.school_id,",
                "t1.station_id,",
                "t1.subjects_id,",
                "t1.department_id,",
                "t1.need_count,",
                "t1.status,",
                "t2.type AS station_type,",
                "t3.`name` AS department_name,",
                "CONCAT(t2.`name`,IFNULL( t4.`name`, '' )) AS station_needs_name,",
                "( CASE WHEN t2.type = 1 THEN t1.school_id ELSE t1.department_id END ) AS school_or_department_id,",
                "( CASE WHEN t2.type = 1 THEN t5.`name` ELSE t3.`name` END ) AS school_or_department_name,",
                "IFNULL(t5.pid,0) as pid",
                "FROM",
                "adms_school_needs AS t1",
                "INNER JOIN adms_station AS t2 ON t1.station_id = t2.id",
                "LEFT JOIN department AS t3 ON t1.department_id = t3.id",
                "LEFT JOIN subjects AS t4 ON t1.subjects_id = t4.id",
                "LEFT JOIN organization AS t5 ON t1.school_id = t5.id ",
                "WHERE",
                "1 = 1 ",
                "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                    "AND t1.school_id in(${schoolId})",
                "</when>",
                "<when test='departmentId!=null and departmentId!=&apos;&apos; '>",
                    "AND t1.department_id in(${departmentId})",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "AND t1.subjects_id in(${subjectsId})",
                "</when>",
                "<when test='type!=null and type!=&apos;&apos; and type != 0'>",
                    "AND t2.type = ${type}",
                "</when>",
                "<when test='stationId!=null and stationId!=&apos;&apos; '>",
                    "AND t1.station_id IN (${stationId})",
                "</when>",
            "),",

            "with_tb02 AS (",
            "SELECT",
            "t1.try_date,",
            "t1.pass_type,",
            "t1.try_audit_date,",
            "t2.`name`,",
            "t2.phone,",
            "t2.inviter_id,",
            "t3.station_id,",
            "t3.subjects_id,",
            "t3.department_id,",
            "t3.labour_id,",
            "t1.school_id,",
            "( CASE WHEN t3.type = 1 THEN t1.school_id ELSE t3.department_id END ) AS school_or_department_id,",
            "( CASE WHEN t3.type = 1 THEN t4.`name` ELSE t5.`name` END ) AS school_or_department_name,",
            "CASE WHEN EXISTS ( SELECT 1 FROM role_admin WHERE admin_id = t2.inviter_id AND role_id = 1568160915052400644 ) THEN 1 ELSE 2 END AS role_id ",
            "FROM",
            "adms_try_job AS t1",
            "INNER JOIN adms_recruit AS t2 ON t2.id = t1.recruit_id",
            "INNER JOIN adms_station_needs AS t3 ON t1.station_needs_id = t3.id",
            "LEFT JOIN organization AS t4 ON t1.school_id = t4.id",
            "LEFT JOIN department AS t5 ON t3.department_id = t5.id ",
            "WHERE",
            "1 = 1 ",
            "AND is_arrive_job = 0 ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "AND t1.school_id in(${schoolId})",
            "</when>",
            "<when test='departmentId!=null and departmentId!=&apos;&apos; '>",
                "AND t3.department_id in(${departmentId})",
            "</when>",
            ")",

            "SELECT",
            "*,",
            "( needCount - tryJobRecruitCount ) AS needRecruitment,",
            "( departmentCount + schoolCount ) AS allocateCount",
            "FROM",
            "(",
                "SELECT",
                "SUM( need_count ) AS needCount,",
                "SUM( departmentCount ) AS departmentCount,",
                "SUM( schoolCount ) AS schoolCount,",
                "SUM( tryJobPassCount ) AS tryJobPassCount,",
                "SUM( tryJobNoPassCount ) AS tryJobNoPassCount,",
                "SUM( tryJobIngCount ) AS tryJobIngCount,",
                "SUM( tryJobRecruitCount ) AS tryJobRecruitCount,",
                "school_or_department_id,",
                "school_or_department_name, ",
                "station_id,",
                "station_needs_name,",
                "station_type,",
                "status,",
                "pid,",
                "subjects_id",
                "FROM",
                "(",
                    "SELECT",
                    "need_count,",

                    "SUM( CASE WHEN role_id = 1 ",
                    "<when test='labourId!=null and labourId!=&apos;&apos; '>",
                        "AND labour_id IN (${labourId})",
                    "</when>",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                        "AND try_date BETWEEN '${startDate}' AND '${endDate}' ",
                    "</when>",
                    "THEN 1 ELSE 0 END ) AS departmentCount,",

                    "SUM( CASE WHEN role_id = 2 ",
                    "<when test='labourId!=null and labourId!=&apos;&apos; '>",
                        "AND labour_id IN (${labourId})",
                    "</when>",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                        "AND try_date BETWEEN '${startDate}' AND '${endDate}' ",
                    "</when>",
                    "THEN 1 ELSE 0 END ) AS schoolCount,",

                    "SUM( CASE WHEN pass_type = 1 ",
                    "<when test='labourId!=null and labourId!=&apos;&apos; '>",
                        "AND labour_id IN (${labourId})",
                    "</when>",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                        "AND try_audit_date BETWEEN '${startDate}' AND '${endDate}' ",
                    "</when>",
                    "THEN 1 ELSE 0 END ) AS tryJobPassCount,",

                    "SUM( CASE WHEN pass_type = 2 ",
                    "<when test='labourId!=null and labourId!=&apos;&apos; '>",
                        "AND labour_id IN (${labourId})",
                    "</when>",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                        "AND try_audit_date BETWEEN '${startDate}' AND '${endDate}' ",
                    "</when>",
                    "THEN 1 ELSE 0 END ) AS tryJobNoPassCount,",

                    "SUM( CASE WHEN pass_type = 0 ",
                    "<when test='labourId!=null and labourId!=&apos;&apos; '>",
                        "AND labour_id IN (${labourId})",
                    "</when>",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                        "AND try_date BETWEEN '${startDate}' AND '${endDate}' ",
                    "</when>",
                    "THEN 1 ELSE 0 END ) AS tryJobIngCount, ",

                    "SUM( CASE WHEN pass_type IN ( 1 ) THEN 1 ELSE 0 END ) AS tryJobRecruitCount,",
                    "with_tb01.school_or_department_id,",
                    "with_tb01.school_or_department_name,",
                    "with_tb01.station_id, ",
                    "with_tb01.station_needs_name, ",
                    "with_tb01.station_type,",
                    "with_tb01.status,",
                    "with_tb01.pid,",
                    "with_tb01.subjects_id",

                    "FROM",
                    "with_tb01",
                    "LEFT JOIN with_tb02 AS t01 ON t01.school_or_department_id = with_tb01.school_or_department_id ",
                    "AND t01.subjects_id = with_tb01.subjects_id ",
                    "AND t01.station_id = with_tb01.station_id ",
                    "GROUP BY",
                    "with_tb01.school_or_department_id,",
                    "with_tb01.station_id,",
                    "with_tb01.subjects_id",
                ") AS t ",
                "GROUP BY",
                "school_or_department_id ",
                "<if test='isDetails == 1'>",
                    ",station_id,subjects_id ",
                "</if>",
            ") AS tt ",
            "WHERE",
            "needCount &gt; 0 ",
            "</script>"})
    List<SchoolNeeds> getAssignListV2(@Param("startDate") String startDate, @Param("endDate") String endDate, @Param("stationNeedsId") String stationNeedsId, @Param("schoolId") String schoolId, @Param("departmentId") String departmentId, @Param("isDetails") Integer isDetails, @Param("type") Integer type, @Param("subjectsId") String subjectsId, @Param("labourId") String labourId, @Param("stationId") String stationId);


    @Select({"<script>",
            "SELECT * FROM (",
                "SELECT",
                "t1.*,",
                "t4.`name` AS stationName,",
                "t5.`name` AS invitation_name,",
                "getSchoolName ( t1.school_id ) AS school_name,",
                "t6.`name` AS department_name,",
                "t2.`name`,",
                "t2.phone,",
                "CASE",
                "WHEN EXISTS ( SELECT 1 FROM role_admin WHERE admin_id = t2.inviter_id AND role_id = 1568160915052400644 ) THEN",
                "1 ELSE 2 ",
                "END AS role_id ",
                "FROM",
                "adms_try_job t1",
                "INNER JOIN adms_recruit t2 ON t2.id = t1.recruit_id",
//                "LEFT JOIN adms_school_needs t3 ON ",
//                "<when test='type==null or type==&apos;&apos; '>",
//                    "(( t3.department_id IS NOT NULL AND t3.department_id = t1.department_id ) OR ( t3.department_id IS NULL AND t3.school_id = t1.school_id ))",
//                "</when>",
//                "<when test='type!=null and type!=&apos;&apos; '>",
//                    "<if test='type == 1'>",
//                        "t3.school_id = t1.school_id ",
//                    "</if>",
//                    "<if test='type == 2'>",
//                        "t3.department_id = t1.department_id ",
//                    "</if>",
//                "</when>",
//                "AND t1.station_needs_id = t3.station_needs_id",
                "INNER JOIN adms_station_needs t4 ON t4.id = t1.station_needs_id",
                "LEFT JOIN staff AS t5 ON t5.id = t2.inviter_id ",
                "LEFT JOIN department AS t6 ON t1.department_id = t6.id",
                "WHERE 1=1",
                "AND is_arrive_job = 0 ",
                "<when test='type!=null and type!=&apos;&apos; '>",
                    "AND t4.type = ${type} ",
                "</when>",
                "<when test='labourId!=null and labourId!=&apos;&apos; '>",
                    "AND t4.labour_id IN ( ${labourId} )",
                "</when>",
                "<when test='stationId!=null and stationId!=&apos;&apos; '>",
                    "AND t4.station_id IN (${stationId})",
                "</when>",
                "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                    "AND t1.school_id in(${schoolId}) ",
                "</when>",
                "<when test='departmentId!=null and departmentId!=&apos;&apos; '>",
                    "AND t1.department_id = ${departmentId} ",
                "</when>",
                "<when test='stationNeedsId!=null and stationNeedsId!=&apos;&apos; '>",
                    "AND t1.station_needs_id = ${stationNeedsId} ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "AND t1.subjects_id in(${subjectsId})",
                "</when>",
            ") AS t01",
            "WHERE 1=1",
            "<when test='detailsType!=null and detailsType!=&apos;&apos; '>",
                "<if test='detailsType == 1'>",
                    "AND role_id = 1",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                        "AND try_date BETWEEN '${startDate}' AND '${endDate}'",
                    "</when>",
                "</if>",

                "<if test='detailsType == 2'>",
                    "AND role_id = 2",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                        "AND try_date BETWEEN '${startDate}' AND '${endDate}'",
                    "</when>",
                "</if>",

                "<if test='detailsType == 3'>",
                    "AND pass_type = 1",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                        "AND try_audit_date BETWEEN '${startDate}' AND '${endDate}'",
                    "</when>",
                "</if>",

                "<if test='detailsType == 4'>",
                    "AND pass_type = 2",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                        "AND try_audit_date BETWEEN '${startDate}' AND '${endDate}'",
                    "</when>",
                "</if>",

                "<if test='detailsType == 5'>",
                    "AND pass_type = 0",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos; '>",
                        "AND try_date BETWEEN '${startDate}' AND '${endDate}'",
                    "</when>",
                "</if>",
            "</when>",
            "ORDER BY t01.try_date DESC" ,
            "</script>"})
    List<TryJob> getAssignDetails(IPage<TryJob> page, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("schoolId") String schoolId, @Param("departmentId") String departmentId, @Param("detailsType") String detailsType, @Param("type") Integer type, @Param("labourId") String labourId, @Param("stationId") String stationId, @Param("stationNeedsId") String stationNeedsId, @Param("subjectsId") String subjectsId);

    @Select({"<script>",
            "SELECT",
            "MIN( DATE ( created_at ) ) min_date,",
            "GREATEST(MAX(DATE(try_audit_date)),MAX(DATE(try_date)),MAX(DATE(created_at))) AS max_date",
            "FROM",
            "adms_try_job",
            "WHERE",
            "is_arrive_job = 0",
            "</script>"})
    TryJob getDate();


    @Select({"<script>",
            "SELECT",
                    "t1.school_id as id,",
                    "(CASE",
                    "WHEN t2.type = 1 THEN",
                    "t4.`name`",
                    "ELSE",
                    "t3.`name`",
                    "END)",
                    " AS name",
                    //"t2.type,",
                    //"t2.department_id,",
                    //"t3.`name` AS department_name ",
                    "FROM",
                    "adms_school_needs AS t1",
                    "INNER JOIN adms_station_needs AS t2 ON t1.station_needs_id = t2.id",
                    "INNER JOIN organization AS t4 ON t1.school_id = t4.id",
                    "LEFT JOIN department AS t3 ON t2.department_id = t3.id ",
                    "where t1.year_part = ${yearPart} ",
                    "and t1.quarter_num = ${quarterNum}",
                    "and t2.status = 1",
                    "GROUP BY",
                    "t1.school_id,",
                    "t2.department_id",
                    "order by t2.sort,t2.id asc",
            "</script>"})
    public List<TidyRs> getAllNeedsSchool(@Param("yearPart") Integer yearPart,
                                          @Param("quarterNum") Integer quarterNum
                                          );


    /**
     * 获取招聘未满的校区
     * @param
     * @param
     * @return
     */
    @Select({"<script>",
            "WITH with_tb01 AS (",
                      "SELECT",
                        "t1.school_id,",
                        "t1.station_id,",
                        "t1.subjects_id,",
                        "t1.department_id,",
                        "t1.need_count,",
                        "t3.`name` AS department_name,",
                        "CONCAT(",
                          "t2.NAME,",
                        "IFNULL( t4.NAME, '' )) AS station_needs_name,",
                        "( CASE WHEN t2.type = 1 THEN t1.school_id ELSE t1.department_id END ) AS school_or_department_id,",
                        "( CASE WHEN t2.type = 1 THEN t5.NAME ELSE t3.`name` END ) AS school_or_department_name ",
                      "FROM",
                        "adms_school_needs AS t1",
                        "INNER JOIN adms_station AS t2 ON t1.station_id = t2.id",
                        "LEFT JOIN department AS t3 ON t1.department_id = t3.id",
                        "LEFT JOIN subjects AS t4 ON t1.subjects_id = t4.id",
                        "LEFT JOIN organization AS t5 ON t1.school_id = t5.id ",
                        "LEFT JOIN (select * from adms_allot_area_set GROUP BY area_id,school_id) AS t6 ON t1.school_id = t6.school_id ",
                      "WHERE",
                        "1 = 1 ",
                        "and t1.status = 1",
                        "and t1.station_id = ${stationId}",
                        "and t1.subjects_id = ${subjectsId}",
                        "<if test='areaId != null and areaId != &apos;&apos;'>",
                            "and t6.area_id = ${areaId}",
                        "</if>",
                      "),",
                      "with_tb02 AS (",
                      "SELECT",
                        "t1.try_date,",
                        "t1.pass_type,",
                        "t1.try_audit_date,",
                        "t2.`name`,",
                        "t2.phone,",
                        "t2.inviter_id,",
                        "t3.station_id,",
                        "t3.subjects_id,",
                        "t3.department_id,",
                        "t3.NAME AS station_needs_name,",
                        "t3.sort,",
                        "t1.school_id,",
                        "( CASE WHEN t3.type = 1 THEN t1.school_id ELSE t3.department_id END ) AS school_or_department_id,",
                        "( CASE WHEN t3.type = 1 THEN t4.NAME ELSE t5.`name` END ) AS school_or_department_name,",
                        "( CASE WHEN EXISTS ( SELECT 1 FROM role_admin WHERE admin_id = t2.inviter_id AND role_id = 1568160915052400644 ) THEN 1 ELSE 2 END ) AS role_id ",
                      "FROM",
                        "adms_try_job AS t1",
                        "INNER JOIN adms_recruit AS t2 ON t2.id = t1.recruit_id",
                        "INNER JOIN adms_station_needs AS t3 ON t1.station_needs_id = t3.id",
                        "INNER JOIN organization AS t4 ON t1.school_id = t4.id",
                        "LEFT JOIN department AS t5 ON t3.department_id = t5.id ",
                      "WHERE",
                        "1 = 1 ",
                        "AND t1.is_arrive_job = 0 ",
                      ")",
                        "SELECT",
                      "school_id AS id,",
                      "school_or_department_name AS NAME,",
                      "school_id,",
                      "school_or_department_id,",
                      "school_or_department_name,",
                      "station_needs_name,",
                      "needCount,",
                      "tryJobPassCount,",
                      "tryJobIngCount,",
                      "sort,",
                      "( needCount - tryJobPassCount - tryJobIngCount ) AS needRecruitment ",
                    "FROM",
                      "(",
                      "SELECT",
                        "school_id,",
                        "school_or_department_id,",
                        "school_or_department_name,",
                        "station_needs_name,",
                        "sum( need_count ) AS needCount,",
                        "sum( tryJobPassCount ) AS tryJobPassCount,",
                        "sum( tryJobIngCount ) AS tryJobIngCount,",
                        "sort ",
                      "FROM",
                        "(",
                        "SELECT",
                          "with_tb01.school_id,",
                          "with_tb01.station_needs_name,",
                          "need_count,",
                          "with_tb01.school_or_department_id,",
                          "with_tb01.school_or_department_name,",
                          "with_tb01.station_id,",
                          "t01.sort,",
                          "SUM( CASE WHEN t01.pass_type = 1 THEN 1 ELSE 0 END ) AS tryJobPassCount,",
                          "SUM( CASE WHEN t01.pass_type = 0 THEN 1 ELSE 0 END ) AS tryJobIngCount ",
                        "FROM",
                          "with_tb01",
                          "LEFT JOIN with_tb02 AS t01 ON t01.school_or_department_id = with_tb01.school_or_department_id ",
                          "AND t01.subjects_id = with_tb01.subjects_id ",
                          "AND t01.station_id = with_tb01.station_id ",
                        "GROUP BY",
                          "with_tb01.school_or_department_id,",
                          "with_tb01.station_id,",
                          "with_tb01.subjects_id ",
                        ") AS t ",
                      "GROUP BY",
                        "school_or_department_id ",
                      ") AS tt ",
                    "HAVING",
                      "( needRecruitment &gt; 0 ) ",
                    "ORDER BY",
                      "sort ASC",
            "</script>"})
    public List<TidyRs> getAllNeedsSchoolForLess(@Param("stationId") Long stationId,
                                          @Param("subjectsId") Long subjectsId,
                                          @Param("areaId") Long areaId
    );

}
